﻿using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Enums;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;

namespace Caraya.MFG.BusinessPlugIn
{
    [Description("二开简单账表-采购物料计算"), HotUpdate]
    public class PurExecPlugIn : SysReportBaseService
    {
        public override void Initialize()
        {
            base.Initialize();
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.ReportName = new LocaleValue("采购物料计算报表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = false;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            //this.ReportProperty.PrimaryKeyFieldName = "FID";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            //List<DecimalControlField> list = new List<DecimalControlField>();
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FQtyUnit"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }
        protected string GetDataByKey(DynamicObject dy, string key)
        {
            if (dy != null && dy[key] != null && !string.IsNullOrWhiteSpace(dy[key].ToString()))
            {
                return dy[key].ToString();
            }
            return string.Empty;
        }
        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            ReportTitles reportTitles = new ReportTitles();
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            IDictionary customParams = filter.CustomParams;
            if (customFilter["FMATERIALID"] is DynamicObject MATERIAL)
            {
                var fnumber = MATERIAL["Number"].ToString();
                reportTitles.AddTitle("FMATERIALID1", fnumber);
                return reportTitles;
            }
            if (customParams["OpenParameter"] is IDictionary dictionary)
            {
                var fnumber = dictionary["FMATERIALID"];
                if (fnumber != null)
                {
                    reportTitles.AddTitle("FMATERIALID1", fnumber.ToString());
                }
            }
            return reportTitles;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            object whereFempId = default;
            object forgId = default;
            var vorgId = this.Context.CurrentOrganizationInfo.ID;
            var OpenParameter = filter.CustomParams["OpenParameter"];
            if (OpenParameter is IDictionary dictionary)
            {
                whereFempId = dictionary["FMATERIALID_Id"];
                forgId = dictionary["F_OrgId"];
            }
            if (whereFempId == null)
            {
                whereFempId = customFilter["FMATERIALID_Id"];
            }
            if (forgId == null)
            {
                forgId = customFilter["F_OrgId"];
                var orgId = customFilter["F_OrgId_Id"];
            }

            string text = @"/*dialect*/
SELECT t1.*
	INTO {0} 
FROM ( 
select * ,
--FENDQTY=ISNULL(LAG(BALANCEQUANTITY) over (partition by MATERIALNAME order by FIDENTITYID),0)
FENDQTY=sum(FBALANCEQUANTITY) over(partition by FMATERIALNAME order by FIDENTITYID)
from (
SELECT ROW_NUMBER() over(Order by FSTOCKSTATUS DESC,FDATE ASC) As FIDENTITYID, * FROM 
(SELECT  '' AS FSALEORDERNO, '' AS FPRODUCTNAME, '' AS FMODEL, 0 AS FQUANTITYOFORDER, 0 AS FNUMBERATOR, 
                0 AS FDENOMINATOR, 0 AS FREQUIREDQUANTITY, e0.FUpdateTime AS FDATE, e2.FName AS FMATERIALNAME, 
                e2.FSpecification AS FSPECIFICATION, 0 AS FUNCLAIMEDNUMBER, e1.FName AS FSTOCKNAME, 
                e3.FName AS FSTOCKSTATUS, e0.FBaseQty AS FINVENTORYQUANTITY, 0 AS FREMAINSTOCKINQTY, 
                e0.FBaseQty AS FBALANCEQUANTITY
FROM      (SELECT   FSTOCKORGID AS FStockOrgId, FSTOCKID AS FStockId, FOWNERTYPEID AS FOwnerTypeId, 
                                 FBASEQTY AS FBaseQty, FSTOCKSTATUSID AS FStockStatusId, FMATERIALID AS FMaterialId, 
                                 '' AS FUpdateTime
                 FROM      T_STK_INVENTORY AS t0
                 WHERE   (FOBJECTTYPEID = 'STK_Inventory')) AS e0 INNER JOIN
                    (SELECT   t0.FSTOCKID AS FStockId, t0_L.FNAME AS FName
                     FROM      T_BD_STOCK AS t0 LEFT OUTER JOIN
                                     T_BD_STOCK_L AS t0_L ON t0.FSTOCKID = t0_L.FSTOCKID AND t0_L.FLOCALEID = 2052) AS e1 ON 
                e0.FStockId = e1.FStockId INNER JOIN
                    (SELECT   t0.FMATERIALID, t0.FNUMBER AS FNumber, t0_L.FSPECIFICATION AS FSpecification, 
                                     t0_L.FNAME AS FName
                     FROM      T_BD_MATERIAL AS t0 LEFT OUTER JOIN
                                     T_BD_MATERIAL_L AS t0_L ON t0.FMATERIALID = t0_L.FMATERIALID AND t0_L.FLOCALEID = 2052) 
                AS e2 ON e0.FMaterialId = e2.FMATERIALID INNER JOIN
                    (SELECT   t0.FSTOCKSTATUSID AS FStockStatusId, t0_L.FNAME AS FName
                     FROM      T_BD_STOCKSTATUS AS t0 LEFT OUTER JOIN
                                     T_BD_STOCKSTATUS_L AS t0_L ON t0.FSTOCKSTATUSID = t0_L.FSTOCKSTATUSID AND 
                                     t0_L.FLOCALEID = 2052) AS e3 ON e0.FStockStatusId = e3.FStockStatusId
WHERE   (e0.FOwnerTypeId = N'BD_OwnerOrg') AND (e0.FStockOrgId = 1) AND (e0.FStockStatusId = 10000) AND 
                (e0.FMaterialId = {1})

union all

SELECT   e0.FSALEORDERNO, m0.FName AS FPRODUCTNAME, m0.FSpecification AS FMODEL, e0.FQty AS FQUANTITYOFORDER, 
                e1.FNumerator AS FNUMBERATOR, e1.FDenominator AS FDENOMINATOR, e1.FMustQty AS FREQUIREDQUANTITY, 
                e3.FPlanFinishDate AS FDATE, e2.FName AS FMATERIALNAME, e2.FSpecification AS FSPECIFICATION, 
                e1.FNoPickedQty AS FUNCLAIMEDNUMBER,'' AS FSTOCKNAME, 
                '' AS FSTOCKSTATUS, 0 AS FINVENTORYQUANTITY, 0 AS FREMAINSTOCKINQTY, 
                -e1.FNoPickedQty AS FBALANCEQUANTITY
FROM      (SELECT   FID, FPRDORGID AS FPrdOrgId, FMATERIALID AS FMaterialID, FQTY AS FQty, FSALEORDERNO
                 FROM      T_PRD_PPBOM AS t0
                 WHERE   (FFORMID = 'PRD_PPBOM')) AS e0 INNER JOIN
                    (SELECT   t0.FID, t1.FDENOMINATOR AS FDenominator, t1.FNUMERATOR AS FNumerator, 
                                     t1.FMOENTRYID AS FMoEntryId1, t1_Q.FNOPICKEDQTY AS FNoPickedQty, t1.FMUSTQTY AS FMustQty, 
                                     t1.FMATERIALID AS FMaterialID2
                     FROM      T_PRD_PPBOM AS t0 LEFT OUTER JOIN
                                     T_PRD_PPBOMENTRY AS t1 ON t0.FID = t1.FID LEFT OUTER JOIN
                                     T_PRD_PPBOMENTRY_Q AS t1_Q ON t1.FENTRYID = t1_Q.FENTRYID
                     WHERE   (t0.FFORMID = 'PRD_PPBOM')) AS e1 ON e0.FID = e1.FID INNER JOIN
                    (SELECT   t1.FENTRYID AS FEntryId, t1.FPLANFINISHDATE AS FPlanFinishDate, t1_A.FSTATUS AS FStatus
                     FROM      T_PRD_MO AS t0 LEFT OUTER JOIN
                                     T_PRD_MOENTRY AS t1 ON t0.FID = t1.FID LEFT OUTER JOIN
                                     T_PRD_MOENTRY_A AS t1_A ON t1.FENTRYID = t1_A.FENTRYID
                     WHERE   (t0.FFORMID = 'PRD_MO')) AS e3 ON e1.FMoEntryId1 = e3.FEntryId INNER JOIN
                    (SELECT   t0.FMATERIALID, t0.FNUMBER AS FNumber, t0_L.FSPECIFICATION AS FSpecification, 
                                     t0_L.FNAME AS FName
                     FROM      T_BD_MATERIAL AS t0 LEFT OUTER JOIN
                                     T_BD_MATERIAL_L AS t0_L ON t0.FMATERIALID = t0_L.FMATERIALID AND t0_L.FLOCALEID = 2052) 
                AS m0 ON e0.FMaterialID = m0.FMATERIALID INNER JOIN
                    (SELECT   t0.FMATERIALID, t0.FNUMBER AS FNumber, t0_L.FSPECIFICATION AS FSpecification, 
                                     t0_L.FNAME AS FName
                     FROM      T_BD_MATERIAL AS t0 LEFT OUTER JOIN
                                     T_BD_MATERIAL_L AS t0_L ON t0.FMATERIALID = t0_L.FMATERIALID AND t0_L.FLOCALEID = 2052) 
                AS e2 ON e1.FMaterialID2 = e2.FMATERIALID
WHERE   (e3.FStatus <> N'6') AND (e3.FStatus <> N'7') AND (e1.FMaterialID2 = {1}) AND (e1.FNoPickedQty > 0)

union all

SELECT   e1.FBillNo AS FSALEORDERNO, '' AS FPRODUCTNAME, '' AS FMODEL, 0 AS FQUANTITYOFORDER, 0 AS FNUMBERATOR, 
                0 AS FDENOMINATOR, 0 AS FREQUIREDQUANTITY, e2.FDeliveryDate AS FDATE, e3.FName AS FMATERIALNAME, 
                e3.FSpecification AS FSPECIFICATION, 0 AS FUNCLAIMEDNUMBER, '' AS FSTOCKNAME, '' AS FSTOCKSTATUS, 
                0 AS FINVENTORYQUANTITY, e2.FRemainStockINQty AS FREMAINSTOCKINQTY, 
                e2.FRemainStockINQty AS FBALANCEQUANTITY
FROM      (SELECT   t0.FID, t0.FPURCHASEORGID AS FPurchaseOrgId, t3_D.FDELIVERYDATE AS FDeliveryDate, 
                                 t3_R.FREMAINSTOCKINQTY AS FRemainStockINQty, t3.FMATERIALID AS FMaterialId, 
                                 t3.FMRPCLOSESTATUS AS FMRPCloseStatus, t3.FMRPTERMINATESTATUS AS FMRPTerminateStatus, 
                                 t0.FCLOSESTATUS AS FCloseStatus
                 FROM      T_PUR_POORDER AS t0 LEFT OUTER JOIN
                                 T_PUR_POORDERENTRY AS t3 ON t0.FID = t3.FID LEFT OUTER JOIN
                                 T_PUR_POORDERENTRY_D AS t3_D ON t3.FENTRYID = t3_D.FENTRYID LEFT OUTER JOIN
                                 T_PUR_POORDERENTRY_R AS t3_R ON t3.FENTRYID = t3_R.FENTRYID
                 WHERE   (t0.FOBJECTTYPEID = 'PUR_PurchaseOrder')) AS e2 INNER JOIN
                    (SELECT   FID, FBILLNO AS FBillNo, FDOCUMENTSTATUS AS FDocumentStatus, FCANCELSTATUS AS FCancelStatus, 
                                     FCLOSESTATUS AS FCloseStatus
                     FROM      T_PUR_POORDER AS t0
                     WHERE   (FOBJECTTYPEID = 'PUR_PurchaseOrder')) AS e1 ON e2.FID = e1.FID INNER JOIN
                    (SELECT   t0.FMATERIALID, t0.FNUMBER AS FNumber, t0_L.FSPECIFICATION AS FSpecification, 
                                     t0_L.FNAME AS FName
                     FROM      T_BD_MATERIAL AS t0 LEFT OUTER JOIN
                                     T_BD_MATERIAL_L AS t0_L ON t0.FMATERIALID = t0_L.FMATERIALID AND t0_L.FLOCALEID = 2052) 
                AS e3 ON e2.FMaterialId = e3.FMATERIALID
WHERE   (e1.FDocumentStatus = N'C') AND (e1.FCancelStatus = N'A') AND (e2.FMRPTerminateStatus = N'A') AND 
                (e2.FMRPCloseStatus = N'A') AND (e2.FRemainStockINQty > 0) AND (e2.FMaterialId = {1}) AND 
                (e2.FCloseStatus = N'A')

) AS TT
) AS BB

group by BB.FIDENTITYID,BB.FSALEORDERNO,BB.FPRODUCTNAME,BB.FMODEL,BB.FQUANTITYOFORDER,BB.FNUMBERATOR,BB.FDENOMINATOR,BB.FREQUIREDQUANTITY,BB.FDATE,
BB.FMATERIALNAME,BB.FSPECIFICATION, BB.FINVENTORYQUANTITY,BB.FUNCLAIMEDNUMBER,BB.FSTOCKNAME,BB.FSTOCKSTATUS,BB.FREMAINSTOCKINQTY,BB.FBALANCEQUANTITY
) t1
";
            var sql = string.Format(text, tableName, whereFempId);
            //DBUtils.ExecuteDynamicObject(this.Context, sql);
            DBServiceHelper.ExecuteDynamicObject(this.Context, sql);
        }


        /// <summary>        
        /// 构建出报表列        
        /// </summary>
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            var localEid = this.Context.UserLocale.LCID;
            header.AddChild("FSALEORDERNO", new LocaleValue("单据编号", localEid));
            header.AddChild("FPRODUCTNAME", new LocaleValue("产品名称", localEid));
            header.AddChild("FMODEL", new LocaleValue("型号规格", localEid));
            header.AddChild("FQUANTITYOFORDER", new LocaleValue("订单数量", localEid));
            header.AddChild("FNUMBERATOR", new LocaleValue("分子", localEid));
            header.AddChild("FDENOMINATOR", new LocaleValue("分母", localEid));
            header.AddChild("FREQUIREDQUANTITY", new LocaleValue("需求数量", localEid));
            header.AddChild("FDATE", new LocaleValue("需求日期", localEid));
            header.AddChild("FMATERIALNAME", new LocaleValue("物料名称", localEid));
            header.AddChild("FSPECIFICATION", new LocaleValue("物料规格", localEid));
            header.AddChild("FINVENTORYQUANTITY", new LocaleValue("库存数量", localEid));
            header.AddChild("FUNCLAIMEDNUMBER", new LocaleValue("未领数量", localEid));
            header.AddChild("FSTOCKNAME", new LocaleValue("仓库", localEid));
            header.AddChild("FSTOCKSTATUS", new LocaleValue("库存状态", localEid));
            header.AddChild("FREMAINSTOCKINQTY", new LocaleValue("采购剩余入库数量", localEid));
            header.AddChild("FENDQTY", new LocaleValue("结存数量", localEid));

            return header;
            //return base.GetReportHeaders(filter);
        }

        /// <summary>
        /// 设置报表合计
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            List<SummaryField> list = new List<SummaryField>();
            SummaryField item = new SummaryField("FREQUIREDQUANTITY", BOSEnums.Enu_SummaryType.SUM);
            list.Add(item);
            item = new SummaryField("FINVENTORYQUANTITY", BOSEnums.Enu_SummaryType.SUM);
            list.Add(item);
            item = new SummaryField("FUNCLAIMEDNUMBER", BOSEnums.Enu_SummaryType.SUM);
            list.Add(item);
            item = new SummaryField("FREMAINSTOCKINQTY", BOSEnums.Enu_SummaryType.SUM);
            list.Add(item);

            return list;
            //return base.GetSummaryColumnInfo(filter);
        }

        public override void CloseReport()
        {
            this.DropTempTable();
        }

    }
}
